package com.xueyi.aixihu.mapper;

import com.xueyi.aixihu.entity.po.DtUsers;
import com.xueyi.aixihu.entity.vo.*;
import com.xueyi.aixihu.reqeust.*;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

/**
 * @author lvyi
 * @date : 2022/8/16
 * @Description : dt_users表的mapper层
 */
public interface LocalDtUsersMapper {

    /**
     * 用户登陆
     *
     * @param phone
     * @param password
     * @return
     */
    @Select(" SELECT * FROM dt_users WHERE phone = #{phone} AND password = #{password} ")
    public DtUsersVo loginDtUsers(@Param("phone") String phone, @Param("password") String password);


    /**
     * 查询出所有角色id
     *
     * @return
     */
    @Select(" SELECT id FROM dt_auth_group ag " + " LEFT JOIN dt_auth_group_access ac ON ac.group_id = ag.id " + " WHERE ag.status = 1 AND ac.uid = #{id} ")
    List<String> findAllRoleId(Integer id);

    /**
     * 修改用户的最后登录ip和时间
     *
     * @param id 用户id
     */
    @Update(" UPDATE dt_users SET last_login_ip = #{ip},last_login_time_new = #{time} WHERE id = #{id} ")
    public void updateIpAndTime(@Param("id") Integer id, @Param("ip") String ip, @Param("time") Date time);

    /**
     * 根据姓名或手机号、用户类型搜索用户
     *
     * @param dtUsersReq
     * @return 角色列表
     */
    @Select(" <script> " + " SELECT du.*,GROUP_CONCAT( DISTINCT IF ( dag.status = 1 , dag.title , null ) ) roleName, GROUP_CONCAT( DISTINCT IF ( ns.is_valid = 1 , ns.name , null ) ) areaName FROM dt_users du " + " LEFT JOIN dt_auth_group_access ag ON ag.uid = du.id " + " LEFT JOIN dt_auth_group dag ON dag.id = ag.group_id " + " LEFT JOIN axh_user_manage_area ma ON ma.user_id = du.id " + " LEFT JOIN dt_news_sort ns ON ns.id = ma.manage_area_id AND ns.id NOT IN (2,3,16)" + " AND dag.status = 1  AND ns.is_valid = 1  " + " <where> " + " du.is_valid IN (1,2) " + " <if test='roleId != null' > " + " AND dag.id = #{roleId} " + " </if> " + " <if test='inputResult != null' > " + " AND (du.phone LIKE '%${inputResult}%' OR du.username LIKE '%${inputResult}%' )" + " </if> " + " <if test='userType == 1' > " + " AND du.user_type = 1 " + " </if> " + " <if test='userType == 0' > " + " AND du.user_type = 0 " + " </if> " + " GROUP BY du.id " + " ORDER BY du.last_login_time_new DESC " + " LIMIT #{startIndex},#{limit} " + " </where> " + " </script> ")
    List<FindDtUsersVo> findDtUsers(DtUsersReq dtUsersReq);

    @Select(" <script> " + " SELECT COUNT(DISTINCT du.id) FROM dt_users du " + " LEFT JOIN dt_auth_group_access ag ON ag.uid = du.id " + " LEFT JOIN dt_auth_group dag ON dag.id = ag.group_id AND dag.status = 1 " + " <where> " + " du.is_valid IN (1,2) " + " <if test='roleId != null' > " + " AND dag.id = #{roleId} " + " </if> " + " <if test='inputResult != null' > " + " AND (du.phone LIKE '%${inputResult}%' OR du.username LIKE '%${inputResult}%' )" + " </if> " + " <if test='userType == 1' > " + " AND du.user_type = 1 " + " </if> " + " <if test='userType == 0' > " + " AND du.user_type = 0 " + " </if> " + " </where> " + " </script> ")
    Integer findDtUsersTotal(DtUsersReq dtUsersReq);

    /**
     * 禁用或者删除用户
     *
     * @param dtUsersReq
     */
    @Update(" UPDATE dt_users SET is_valid = #{isValid} WHERE id = #{id} ")
    void delOrDisDtUsers(DtUsersReq dtUsersReq);

    /**
     * 修改用户密码
     *
     * @param password
     */
    @Update(" UPDATE dt_users SET password = #{password} WHERE id = #{id} ")
    void updatePassword(String password, Integer id);

    /**
     * 根据手机号查询是否存在
     *
     * @param phone
     * @return
     */
    @Select(" SELECT id FROM dt_users WHERE phone = #{phone} AND is_valid IN (1,2) ")
    Integer findPhoneIsNo(String phone);

    /**
     * 新增后台用户
     *
     * @param dtUsersAddReq
     */
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert(" INSERT INTO dt_users " + " (username,password,avatar,phone,last_login_ip,is_valid,create_time,wx_open_id,last_login_time_new,user_type,expire_time) " + " VALUES(#{username},#{password},#{avatar},#{phone},#{lastLoginIp},#{isValid},#{createTime},#{wxOpenId},#{lastLoginTimeNew},#{userType},#{expireTime}) ")
    void addDtUsers(DtUsersAddReq dtUsersAddReq);

    /**
     * 新增后台用户时新增角色
     *
     * @param
     */
//    @Insert(" INSERT INTO dt_auth_group_access " +
//            " (uid,group_id) " +
//            " VALUES (#{id},#{roleId}) ")
    @Insert({"<script>", "INSERT INTO dt_auth_group_access (uid,group_id)  ", " values ", "<foreach collection='roleIds' item='item' index='index' separator=','>", "(", " #{id}, #{item}", ")", "</foreach>", "</script>"})
    void addRoleByDtUsers(@Param("id") Integer id, @Param("roleIds") List<String> roleIds);

    /**
     * 新增后台用户时新增管理的地址id
     *
     * @param
     */
    @Insert({"<script>", "INSERT INTO axh_user_manage_area (user_id,manage_area_id)  ", " values ", "<foreach collection='manageAreaIds' item='item' index='index' separator=','>", "(", " #{id}, #{item}", ")", "</foreach>", "</script>"})
    void addManageAreaByDtUsers(@Param("id") Integer id, @Param("manageAreaIds") List<String> manageAreaIds);

    /**
     * 修改后台用户信息
     *
     * @param dtUsersUpdateReq
     */
    @Update(" UPDATE dt_users SET" + " username = #{username},avatar = #{avatar}, " + " phone = #{phone},user_type = #{userType},expire_time = #{expireTime} " + " WHERE id = #{id} ")
    public void updateDtUsers(DtUsersUpdateReq dtUsersUpdateReq);

    @Select(" SELECT * FROM dt_users WHERE id = #{id} ")
    DtUsers findTime(Integer id);

    /**
     * 删除用户所绑定的角色
     *
     * @param id 用户id
     */
    @Delete(" DELETE FROM dt_auth_group_access WHERE uid = #{id} ")
    void deleteDtUsersRole(Integer id);

    /**
     * 删除用户所绑定的管理地址
     *
     * @param id 用户id
     */
    @Delete(" DELETE FROM axh_user_manage_area WHERE user_id = #{id} ")
    void deleteDtUsersManageArea(Integer id);


    /**
     * 修改时回显的数据
     *
     * @param id 用户id
     * @return
     */
    @Select(" SELECT " + " du.id, " + " du.username, " + " du.avatar, " + " du.email, " + " du.phone, " + " du.user_type, " + " du.expire_time, " + " GROUP_CONCAT( DISTINCT IF ( au.`status` = 1, ag.group_id, NULL ) ) roleId, " + " GROUP_CONCAT( DISTINCT ma.manage_area_id ) areaId  " + " FROM " + " dt_users du " + " LEFT JOIN dt_auth_group_access ag ON ag.uid = du.id " + " LEFT JOIN dt_auth_group au ON au.id = ag.group_id " + " LEFT JOIN axh_user_manage_area ma ON ma.user_id = du.id " + " LEFT JOIN dt_news_sort ns ON ns.id = ma.manage_area_id " + " AND ns.is_valid = 1 AND au.`status` = 1  " + " WHERE " + " du.id = #{id} ")
    public DtUsersSelectVo findDtUsersById(Integer id);

    @Select("SELECT * FROM dt_users WHERE id = #{id} ")
    DtUsersVo findUserById(Integer id);

    /**
     * 查询出各种类型的人数
     *
     * @return
     */
    @Select(" SELECT " + " COUNT( " + " IF " + " ( is_valid = 1 AND user_type = 1, id, NULL )) normalSize, " + " COUNT( " + " IF " + " ( is_valid = 2 AND user_type = 1, id, NULL )) disabledSize, " + " COUNT( " + " IF " + " ( is_valid = 1 AND user_type = 0 AND expire_time > now() , id, NULL )) touristsSize " + " FROM " + " dt_users ")
    DtUsersNumberVo findSize();

    /**
     * 用户类型  1：正常 0：游客
     *
     * @return
     */
    @Select(" SELECT user_type id FROM dt_users " + " GROUP BY user_type ")
    List<SelectVo> findUsertype();


    /**
     * 登录日志
     *
     * @return
     */
    @Select(" SELECT " + " du.phone, " + " du.username, " + " SUBSTRING_INDEX( ul.log_content,'】', -1 ) ip, " + " ul.create_time loginTime " + " FROM " + " dt_users du " + " LEFT JOIN axh_user_log ul ON ul.command_user_Id = du.id " + " WHERE " + " ul.log_content LIKE '【用户登录】%' " + " ORDER BY ul.create_time DESC " + " LIMIT #{startIndex},#{limit} ")
    List<DtUsersLogVo> findDtUsersLoginLog(LoginLogReq loginLogReq);


    /**
     * 首页的平台日志
     *
     * @return
     */
    @Select(" SELECT" + " du.avatar, " + " du.username, " + " ul.create_time, " + " SUBSTRING_INDEX( ul.log_content,'】', -1 ) logContent " + " FROM " + " axh_user_log ul " + " LEFT JOIN dt_users du ON du.id = ul.command_user_Id " + " WHERE ul.is_valid = 1 AND from_to_area_id IN (${manageId})" + " ORDER BY ul.create_time DESC " + " LIMIT #{req.startIndex},#{req.limit} ")
    List<DtUsersPlatformLogVo> findDtUsersPlatformLog(@Param("req") DtUsersPlatformReq dtUsersPlatformReq, @Param("manageId") String manageId);


    /**
     * 用户修改自己的密码
     *
     * @param updatePasswordByLoginUserReq
     */
    @Update(" UPDATE dt_users SET password = #{newPassword} WHERE id = #{id} ")
    void updatePasswordByLoginUser(UpdatePasswordByLoginUserReq updatePasswordByLoginUserReq);


    /**
     * 修改个人信息里的头像以及用户名
     *
     * @param updateInfoByLoginUserReq
     */
    @Update(" UPDATE dt_users SET avatar = #{avatar}, username = #{username} WHERE id = #{id} ")
    void updateInfoByLoginUser(UpdateInfoByLoginUserReq updateInfoByLoginUserReq);

    /**
     * 查询个人信息(头像以及用户名)
     *
     * @param id
     * @return
     */
    @Select(" SELECT username,avatar FROM dt_users WHERE id = #{id} ")
    DtUsers findInfoByLoginUser(Integer id);

    /**
     * 根据用户id解除绑定微信
     *
     * @param id 用户id
     */
    @Update(" UPDATE dt_users SET wx_open_id = null WHERE id = #{id} ")
    void unbindWeChat(Integer id);
}
